package org.xt.module.api;

import java.io.File;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Random;

import javax.servlet.http.HttpSession;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.nutz.dao.Cnd;
import org.nutz.dao.Dao;
import org.nutz.dao.QueryResult;
import org.nutz.dao.Sqls;
import org.nutz.dao.entity.Record;
import org.nutz.dao.pager.Pager;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.sql.SqlCallback;
import org.nutz.ioc.loader.annotation.Inject;
import org.nutz.ioc.loader.annotation.IocBean;
import org.nutz.json.Json;
import org.nutz.lang.random.R;
import org.nutz.lang.util.NutMap;
import org.nutz.mvc.annotation.At;
import org.nutz.mvc.annotation.By;
import org.nutz.mvc.annotation.Filters;
import org.nutz.mvc.annotation.Ok;
import org.nutz.mvc.annotation.POST;
import org.nutz.mvc.annotation.Param;
import org.xt.constants.Const;
import org.xt.constants.Res;
import org.xt.entity.DataSummary;
import org.xt.entity.DataDetailsAuto;
import org.xt.entity.DataDetailsAutoTemp;
import org.xt.entity.DataGJEHState;
import org.xt.entity.User;
import org.xt.service.DataSummaryService;
import org.xt.service.PermissionService;

import com.alibaba.fastjson.JSON;
@IocBean
@At("/api/dataDetails/auto")
public class DataDetailsAutoApiModule {
	@Inject
	private Dao dao;
	
	
	@At("/list")
	@Ok("json")
	public Res list(@Param(value="page", df="1")int page, @Param(value="size", df="10")int size
			, @Param(value="machine", df="")String machine
			, @Param(value="item", df="")String item
			, @Param(value="gjehState", df="")String gjehState
			, @Param(value="tempputState", df="")String tempputState
			, @Param(value="liaohao", df="")String liaohao
			, @Param(value="gjNo", df="")String gjNo
			, @Param(value="daChuwei", df="")String daChuwei
			, @Param(value="xiaoChuwei", df="")String xiaoChuwei){
		Pager pager = dao.createPager(page, size);
		Cnd cnd = Cnd.where("dataMachine", "like", "%" + machine + "%")
				.and("dataItem", "like", "%" + item + "%")
				.and("dataGJEHState", "like", "%" + gjehState + "%")
				.and("dataTempputState", "like", "%" + tempputState + "%")
				.and("liaohao", "like", "%" + liaohao + "%")
				.and("gjNo", "like", "%" + gjNo + "%")
				.and("daChuwei", "like", "%" + daChuwei + "%")
				.and("xiaoChuwei", "like", "%" + xiaoChuwei + "%");
		pager.setRecordCount(dao.count(DataDetailsAuto.class, cnd));
		List<DataDetailsAuto> list = dao.query(DataDetailsAuto.class, cnd, pager);
		return Res.NEW().code(Res.SUCCESS).msg("获取数据OK").data(new QueryResult(list, pager));
	}
	
	@At("/exportExcel")
	@Ok("json")
	@Filters
	public Res exportExcel(@Param(value="machine", df="")String machine
			, @Param(value="item", df="")String item
			, @Param(value="gjehState", df="")String gjehState
			, @Param(value="tempputState", df="")String tempputState
			, @Param(value="liaohao", df="")String liaohao
			, @Param(value="gjNo", df="")String gjNo
			, @Param(value="daChuwei", df="")String daChuwei
			, @Param(value="xiaoChuwei", df="")String xiaoChuwei){
		Cnd cnd = Cnd.where("dataMachine", "like", "%" + machine + "%")
				.and("dataItem", "like", "%" + item + "%")
				.and("dataGJEHState", "like", "%" + gjehState + "%")
				.and("dataTempputState", "like", "%" + tempputState + "%")
				.and("liaohao", "like", "%" + liaohao + "%")
				.and("gjNo", "like", "%" + gjNo + "%")
				.and("daChuwei", "like", "%" + daChuwei + "%")
				.and("xiaoChuwei", "like", "%" + xiaoChuwei + "%");
		List<DataDetailsAuto> list = dao.query(DataDetailsAuto.class, cnd);
		
		try(HSSFWorkbook workbook = new HSSFWorkbook();){
			HSSFSheet sheet = workbook.createSheet("Sheet 1");
			HSSFRow header = sheet.createRow(0);
			String[][] keys = {{"SN", "getSn"}, {"机种", "getDataMachine"}, {"类型", "getDataItem"}
			, {"状态", "getDataGJEHState"}, {"暂放状态", "getDataTempputState"}, {"钢架编号", "getGjNo"}
			, {"大储位", "getDaChuwei"}, {"小储位", "getXiaoChuwei"}, {"料号", "getLiaohao"}
			, {"Input时间", "getUpdateTime"}};
		
			for(int i = 0; i < keys.length; i ++){
				header.createCell(i).setCellValue(keys[i][0]);
			}
		
			for(DataDetailsAuto wms : list){
				HSSFRow row = sheet.createRow(sheet.getLastRowNum() + 1);
				Class<?> wmsClass = DataDetailsAuto.class;
				SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				for(int i = 0; i < keys.length; i ++){
					Method method = wmsClass.getMethod(keys[i][1]);
					Object result = method.invoke(wms);
					if(result == null){
						row.createCell(i).setCellValue("");
					}else if(result instanceof Date){
						row.createCell(i).setCellValue(sf.format((Date)result));
					}else if(result instanceof String){
						String r = (String) result;
						if(r.endsWith("BMP") || r.endsWith("bmp") || r.endsWith("JPG") || r.endsWith("jpg")
								 || r.endsWith("JPEG") || r.endsWith("jpeg") || r.endsWith("PNG") || r.endsWith("png")
								 || r.endsWith("gif") || r.endsWith("GIF")){
							row.createCell(i).setCellValue("http://172.22.249.119:8081/ImageProject/Wms/" + r);
						}else{
							row.createCell(i).setCellValue(r);
						}
					}else if(result instanceof Double){
						row.createCell(i).setCellValue((double)result);
					}else if(result instanceof Integer){
						row.createCell(i).setCellValue((int)result);
					}else if(result instanceof Float){
						row.createCell(i).setCellValue((float)result);
					}
				}
				
			}
			String fileName = "SFRD" + R.UU32().replace("-", "") + System.nanoTime() + ".xls";
			File di = new File("C:\\zheshiyigehenzhongyaodewenjian\\buyaoshanwo\\ZhengshiShabi\\SFRD\\");
			
			System.out.println(di.getAbsolutePath() + "\n" + fileName);
			if(!di.exists()){
				di.mkdirs();
			}
			File file = new File(di, fileName);
			if(!file.exists()){
				file.createNewFile();
			}
			workbook.write(file);
			return Res.NEW().code(Res.SUCCESS).msg("ok").data(fileName);
		}catch(Exception e){
			e.printStackTrace();
			return Res.NEW().code(Res.ERROR).msg("导出失败了: " + e.getMessage());
		}
	}
	@At("/downloadExcel")
	@Ok("raw")
	@Filters
	public File downloadExcel(String filename){
		return  new File("C:\\zheshiyigehenzhongyaodewenjian\\buyaoshanwo\\ZhengshiShabi\\SFRD\\", filename);
	}
	
	@At("/in")
	@Ok("json")
	public Res in(@Param("..")DataDetailsAuto details){
		DataDetailsAuto dbDetils = dao.fetch(DataDetailsAuto.class,  Cnd.where("sn", "=", details.getSn()));
		
		if(dbDetils != null){
			details.setId(dbDetils.getId());
			details.setUpdateTime(new Date());
			if(dao.updateIgnoreNull(details) > 0){
				return Res.NEW().code(Res.SUCCESS).msg("In 自动登入成功");
			}else{
				return Res.NEW().code(Res.ERROR).msg("In 自动登入失败");
			}
		}else{
			details.setCreateTime(new Date());
			details.setUpdateTime(new Date());
			details = dao.insert(details);
			if(details != null){
				return Res.NEW().code(Res.SUCCESS).msg("In 自动登入成功");
			}else{
				return Res.NEW().code(Res.ERROR).msg("In 自动登入失败");
			}
		}
	}
	
	public static int num = 0;
	public static int box = 0;
	public static int MAXNUM = 10;
	@At("/xjxcin")
	@Ok("json")
	@POST
	public Res xjxcin(@Param("..")DataDetailsAutoTemp details){
		DataDetailsAutoTemp dbDetils = dao.fetch(DataDetailsAutoTemp.class,  Cnd.where("sn", "=", details.getSn()));
		List<DataDetailsAutoTemp> list = dao.query(DataDetailsAutoTemp.class, null);
		if(list != null && list.size() > 0){
			if(!list.get(0).getLiaohao().equals(details.getLiaohao())){
				NutMap nm = NutMap.NEW().addv("num", num).addv("box", String.format("%05d", box));
				return Res.NEW().code(Res.ERROR).msg("你这次刷的料号与本箱之前刷的不一样，于是绕了一大圈不让你刷进去").data(nm);
			}
		}
		
		details.setBox(box);
		if(dbDetils != null){
			details.setId(dbDetils.getId());
			details.setUpdateTime(new Date());
			if(dao.updateIgnoreNull(details) > 0){
				if(num < MAXNUM){
					NutMap nm = NutMap.NEW().addv("num", num).addv("box", String.format("%05d", box));
					return Res.NEW().code(Res.SUCCESS).msg("In 写入缓存成功").data(nm);
				}else{
					NutMap nm = NutMap.NEW().addv("num", num).addv("box", String.format("%05d", box));
					return Res.NEW().code(Res.SUCCESS).msg("本箱已经有10台了").data(nm);
				}
			}else{
				return Res.NEW().code(Res.ERROR).msg("In 写入缓存失败");
			}
		}else{
			details.setCreateTime(new Date());
			details.setUpdateTime(new Date());
			details = dao.insert(details);
			if(details != null){
				num ++;
				if(num < MAXNUM){
					NutMap nm = NutMap.NEW().addv("num", num).addv("box", String.format("%05d", box));
					return Res.NEW().code(Res.SUCCESS).msg("In 写入缓存成功").data(nm);
				}else{
					NutMap nm = NutMap.NEW().addv("num", num).addv("box", String.format("%05d", box));
					nm.addv("code", 1);
					return Res.NEW().code(Res.SUCCESS).msg("本箱已经有10台了").data(nm);
				}
			}else{
				return Res.NEW().code(Res.ERROR).msg("In 写入缓存失败");
			}
		}
	}
	@At("/writeDB")
	@Ok("json")
	@POST
	public Res writeDB(){
		List<DataDetailsAutoTemp> list = dao.query(DataDetailsAutoTemp.class, null);
		if(list == null && list.size() != 10){
			return Res.NEW().code(Res.ERROR).msg("缓存里不是10台，写入失败");
		}
		try{
			for(DataDetailsAutoTemp t: list){
				DataDetailsAuto da = Json.fromJson(DataDetailsAuto.class, Json.toJson(t));
				DataDetailsAuto dbDA = dao.fetch(DataDetailsAuto.class, Cnd.where("sn", "=", da.getSn()));
				if(dbDA != null){
					da.setId(dbDA.getId());
					dao.updateIgnoreNull(da);
				}else{
					dao.insert(da);
				}
			}
			num = 0;
			box = getMaxBox();
			dao.delete(list);
			return Res.NEW().code(Res.SUCCESS).msg("写入成功")
					.data(NutMap.NEW().addv("delete", dao.delete(list))
					.addv("num", num).addv("box", String.format("%05d", box)));
		}catch(Exception e){
			e.printStackTrace();
			return Res.NEW().code(Res.ERROR).msg("写入失败: " + e.getMessage());
		}
	}
	@At("/clearCache")
	@Ok("json")
	@POST
	public Res clearCache(){
		try{
			num = 0;
			box = getMaxBox();
			List<DataDetailsAutoTemp> list = dao.query(DataDetailsAutoTemp.class, null);
			return Res.NEW().code(Res.SUCCESS).msg("清空成功")
					.data(NutMap.NEW().addv("delete", dao.delete(list))
					.addv("num", num).addv("box", String.format("%05d", box)));
		}catch(Exception e){
			e.printStackTrace();
			return Res.NEW().code(Res.ERROR).msg("清空失败: " + e.getMessage());
		}
	}
//	@At("/jixuShangyici")
//	@Ok("json")
//	@POST
//	public Res jixuShangyici(){
//		try{
//			List<Data1FGjehDetailsAutoTemp> list = dao.query(Data1FGjehDetailsAutoTemp.class, null);
//			if(list != null && list.size() > 0){
//				num = list.size();
//				box = list.get(0).getBox();
//			}else{
//				num = 0;
//				box = getMaxBox();
//			}
//			return Res.NEW().code(Res.SUCCESS).msg("ok")
//					.data(NutMap.NEW()
//					.addv("num", num).addv("box", String.format("%05d", box)));
//		}catch(Exception e){
//			e.printStackTrace();
//			return Res.NEW().code(Res.ERROR).msg("error: " + e.getMessage());
//		}
//	}
	
	@At("/boxlist")
	@Ok("json")
	@POST
	public Res boxlist(){
		return Res.NEW().code(Res.SUCCESS).msg("ok").data(dao.query(DataDetailsAutoTemp.class, null));
	}
	
	private int getMaxBox(){
		Sql sql = Sqls.create("select top 1 box from tb_data_1fgjeh_details_auto order by box desc");
		sql.setCallback(new SqlCallback() {
			@Override
			public List<Integer> invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				// TODO Auto-generated method stub
				List<Integer> list = new ArrayList<>();
				while(rs.next()){
					list.add(rs.getInt("box"));
				}
				return list;
			}
		});
		dao.execute(sql);
		return sql.getList(Integer.class).get(0) + 1;
	}
	
	
	@At("/out")
	@Ok("json")
	public Res out(String sn){
		DataDetailsAuto dbDetils = dao.fetch(DataDetailsAuto.class,  Cnd.where("sn", "=", sn));
		if(dbDetils != null){
			dao.delete(dbDetils);
			return Res.NEW().code(Res.SUCCESS).msg("OUT 自动登出成功");
		}else{
			return Res.NEW().code(Res.ERROR).msg("OUT 自动登出失败");
		}
		
	}
	
	@At("/biangeng")
	@Ok("json")
	public Res biangeng(String oldgjNo, String olddaChuwei, String oldxiaoChuwei
			, String newgjNo, String newdaChuwei, @Param(value="newxiaoChuwei", df="")String newxiaoChuwei){
		Cnd cnd = Cnd.where("dataMachine", "like", "%%")
				.and("dataItem", "like", "%%")
				.and("dataGJEHState", "like", "%%")
				.and("dataTempputState", "like", "%%")
				.and("liaohao", "like", "%%")
				.and("gjNo", "like", "%" + oldgjNo + "%")
				.and("daChuwei", "like", "%" + olddaChuwei + "%")
				.and("xiaoChuwei", "like", "%" + oldxiaoChuwei + "%");
		List<DataDetailsAuto> list = dao.query(DataDetailsAuto.class, cnd);
		for(int i = 0; i < list.size(); i ++){
			list.get(i).setGjNo(newgjNo);
			list.get(i).setDaChuwei(newdaChuwei);
			list.get(i).setXiaoChuwei(newxiaoChuwei);
		}
		int sum = dao.updateIgnoreNull(list);
		if(sum > 0){
	
			return Res.NEW().code(Res.SUCCESS).msg("你这次变更了 " + sum + " 条记录");
		}
		return Res.NEW().code(Res.ERROR).msg("变更失败");
	}
	
	@At("/outByChuwei")
	@Ok("json")
	public Res outByChuwei(String oldgjNo, String olddaChuwei, String oldxiaoChuwei){
		Cnd cnd = Cnd.where("gjNo", "like", "%" + oldgjNo + "%")
				.and("daChuwei", "like", "%" + olddaChuwei + "%")
				.and("xiaoChuwei", "like", "%" + oldxiaoChuwei + "%");
		List<DataDetailsAuto> list = dao.query(DataDetailsAuto.class, cnd);
		int sum = dao.delete(list);
		if(sum > 0){
			return Res.NEW().code(Res.SUCCESS).msg("你这次OUT了 " + sum + " 条记录");
		}
		return Res.NEW().code(Res.ERROR).msg("OUT失败");
	}
}
